<?php
/*
 * @Author: your name
 * @Date: 2021-11-03 15:16:15
 * @LastEditTime: 2021-12-21 14:00:39
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: \property\app\Models\admin_model\Column.php
 */

namespace App\Models\admin_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Store extends Model
{
    use HasFactory;
    static string $tableName = 'store';

    public static function list($whereStr = '', $limitStr = '',$field='*')
    {
        return DB::select(' SELECT `store`.*,`admins`.`nickName`,`admins`.`realName` FROM ' . self::$tableName . ' LEFT JOIN `admins` ON `admins`.`id` = `store`.`admin_id`     WHERE  `store`.`delete_time` is null ' . $whereStr . ' ORDER BY `top_time` DESC,`sort` DESC , `id` DESC  ' . $limitStr);
    }
    
    public static function count($whereStr = '')
    {
        return DB::select(' SELECT COUNT(*) as `count` FROM ' . self::$tableName . '   WHERE  `delete_time` is null ' . $whereStr );
    }

    public static function storeAdminSelect(){
        return DB::select(' SELECT admins.`id` as `value` ,  CASE WHEN  admins.`realName` != "" THEN  concat(admins.`nickName`,"(",admins.`realName`,")") ELSE admins.`nickName` END as `title`  FROM `store` INNER JOIN `admins` ON `admins`.`id` = `store`.`admin_id` WHERE `store`.`delete_time` is null  GROUP BY `admins`.`id` ORDER BY `admins`.`top_time` DESC, `admins`.`id` DESC        ');
    }
    

    public static function storeTagList(){
        // echo " SELECT GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(`tagList`,",", b.help_topic_id+1),",",-1) ) AS ".self::$tableName."  FROM `store` JOIN mysql.help_topic b ON b.help_topic_id < ( length( store.tagList ) - length( REPLACE ( store.tagList, ',', '' )) + 1 )  WHERE `store`.`delete_time` is null  ";
      
        return DB::select(' SELECT GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(`tagList`,",", b.help_topic_id+1),",",-1) ) AS `tagList`  FROM '.self::$tableName.' JOIN mysql.help_topic b ON b.help_topic_id < ( length( store.tagList ) - length( REPLACE ( store.tagList, ",", "" )) + 1 )  WHERE `store`.`delete_time` is null  ');
    }
   
    public static function storeWebList($whereStr = '', $limitStr = ''){
        return DB::select(' SELECT `store`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name` FROM '.self::$tableName.' as `store` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `store`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `store`.`community_id`   WHERE `store`.`delete_time` is null AND `store`.`shelves` = 1 '.$whereStr.' ORDER BY `store`.`top_time` DESC,`store`.`sort` DESC,`store`.`id` DESC '.$limitStr);
    }
    

    public static function storeSelOne($id){
        // echo ' SELECT `store`.*,`admins`.`nickName` as `admin_nickName`,`admins`.`realName` as `admin_realName`,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name` FROM `store`  LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `store`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `store`.`community_id` LEFT JOIN `admins` as `admins`.`id` = `store`.`admin_id`  WHERE `store`.`id` = '.$id.' LIMIT 1';
        return DB::select(' SELECT `store`.*,`admins`.`nickName` as `admin_nickName`,`admins`.`realName` as `admin_realName`,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name` FROM `store`  LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `store`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `store`.`community_id` LEFT JOIN `admins` ON `admins`.`id` = `store`.`admin_id`  WHERE `store`.`id` = '.$id.' LIMIT 1');
    }
    public static function storeSelOneCollect($id,$openid){
        // echo ' SELECT `store`.*,`admins`.`nickName` as `admin_nickName`,`admins`.`realName` as `admin_realName`,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name` FROM `store`  LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `store`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `store`.`community_id` LEFT JOIN `admins` as `admins`.`id` = `store`.`admin_id`  WHERE `store`.`id` = '.$id.' LIMIT 1';
        return DB::select(' SELECT `store`.*,CASE WHEN `collection`.`id` IS NOT NULL THEN 1 ELSE 0 END as `collection`,`admins`.`nickName` as `admin_nickName`,`admins`.`realName` as `admin_realName`,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name` FROM `store`  LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `store`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `store`.`community_id` LEFT JOIN `admins` ON `admins`.`id` = `store`.`admin_id` LEFT JOIN `find_shop_collection_history` as `collection` ON `collection`.`type` = 2 AND `collection`.`table` = "store" AND `collection`.`user_openid` = "'.$openid.'"  AND `collection`.`target_id` = `store`.`id`      WHERE `store`.`id` = '.$id.' LIMIT 1');
    }
    public static function storeWebListRand($id = 0 , $limit = 4 ){
        // echo '  SELECT  `store`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title` FROM  store as `store` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `store`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `store`.`delete_time` is null AND `store`.`shelves` = 1    '.$whereStr.' ORDER BY `store`.`top_time` DESC , `store`.`sort` DESC , `store`.`id` DESC  '.$limitStr;
        return DB::select('  SELECT `store`.`level_des`,`store`.`id`,`store`.`meters_money`,`store`.`cover_image`,`store`.`spaceNum`,`store`.`longitude` ,`store`.`latitude`,`store`.`title`  FROM  '.self::$tableName.'   WHERE `store`.`delete_time` is null AND `store`.`shelves` = 1 AND `store`.`id` != '.$id.' order by rand() limit 0,'.$limit);
    }

}
